package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.canin.model.Leash;
import com.canin.util.CaninUtil;

public class LeashDAO
{
    private Connection conn = null;

    private Map<String, String> sizes = CaninUtil.initializeSizes();

    public LeashDAO(Connection conn)
    {
        this.conn = conn;
    }

    public ArrayList<Leash> getAllLeash() throws Exception
    {
        ArrayList<Leash> leashList = new ArrayList<Leash>();

        String sql = "select * from leash";
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Leash leash = new Leash();
            leash.setId( rs.getInt( "id" ) );
            leash.setName( rs.getString( "name" ) );
            leash.setBrand( rs.getString( "brand" ) );
            leash.setDescription( rs.getString( "description" ) );
            leash.setSize( rs.getString( "size" ) );
            leash.setWidht( rs.getDouble( "width" ) );
            leash.setLength( rs.getDouble( "length" ) );
            leash.setRawMaterial( rs.getString( "rawMaterial" ) );
            leash.setShippingPrice( rs.getInt( "shippingPrice" ) );
            leash.setPrice( rs.getInt( "price" ) );
            
            leash.setThaiSize( sizes.get( leash.getSize() ) );
            
            leashList.add( leash );
        }

        rs.close();
        ps.close();

        return leashList;
    }
	
	public TreeMap<String, String> getLeashBrand() throws Exception
	{
		TreeMap<String, String> leashBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(brand) from leash where brand!=''";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			leashBrandList.put( rs.getString( "brand" ), rs.getString( "brand" ) );
		}

		rs.close();
		ps.close();
		
		return leashBrandList;
	}
	
	public void addNewLeash( Leash leash ) throws Exception
	{
		String sql = "insert into leash(name, brand, description, size, price, shippingPrice, width, length, rawMaterial)" +
				" value(?,?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, leash.getName());
		ps.setString(2, leash.getBrand());
		ps.setString(3, leash.getDescription());
		ps.setString(4, leash.getSize());
		ps.setInt(5, leash.getPrice());
		ps.setInt(6, leash.getShippingPrice());
		ps.setDouble(7, leash.getWidht());
		ps.setDouble(8, leash.getLength());
		ps.setString(9, leash.getRawMaterial());
		
		ps.executeUpdate();
		ps.close();
	}

    public ArrayList<Leash> getLeashBySize( String size ) throws Exception
    {
        ArrayList<Leash> leashList = new ArrayList<Leash>();

        String sql = "select * from leash where size=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, size);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Leash leash = new Leash();
            leash.setId( rs.getInt( "id" ) );
            leash.setName( rs.getString( "name" ) );
            leash.setBrand( rs.getString( "brand" ) );
            leash.setDescription( rs.getString( "description" ) );
            leash.setSize( rs.getString( "size" ) );
            leash.setWidht( rs.getDouble( "width" ) );
            leash.setLength( rs.getDouble( "length" ) );
            leash.setRawMaterial( rs.getString( "rawMaterial" ) );
            leash.setShippingPrice( rs.getInt( "shippingPrice" ) );
            leash.setPrice( rs.getInt( "price" ) );
            leash.setImageName( rs.getString( "imageName" ) );
            
            leash.setThaiSize( sizes.get( leash.getSize() ) );
            
            leashList.add( leash );
        }

        rs.close();
        ps.close();

        return leashList;
    }
    
    
}
